<?php
/**
 * 独立数据库操作类
 * 基于ThinkPHP6 Db类思想封装，移除框架依赖
 */
class Db
{
    /**
     * 数据库连接实例
     * @var \PDO
     */
    protected $pdo;

    /**
     * 数据库配置
     * @var array
     */
    public $config = [
        'type'     => 'mysql',
        'host'     => '127.0.0.1',
        'database' => '***',
        'username' => '***',
        'password' => '***',
        'charset'  => 'utf8mb4',
        'port'     => 3306,
        'prefix'   => '',
    ];

    /**
     * 当前SQL语句
     * @var string
     */
    protected $sql;

    /**
     * 查询参数
     * @var array
     */
    protected $bind = [];

    /**
     * 表名
     * @var string
     */
    protected $table;

    /**
     * WHERE条件
     * @var string
     */
    protected $where = '';

    /**
     * ORDER排序
     * @var string
     */
    protected $order = '';

    /**
     * LIMIT限制
     * @var string
     */
    protected $limit = '';

    /**
     * 字段列表
     * @var string
     */
    protected $field = '*';

    /**
     * 构造函数
     * @param array $config 数据库配置
     * @throws \Exception
     */
    public function __construct(array $config = [])
    {
        // 合并配置
        $this->config = array_merge($this->config, $config);
        
        // 连接数据库
        $this->connect();
    }

    /**
     * 连接数据库
     * @throws \Exception
     */
    protected function connect()
    {
        try {
            $dsn = "{$this->config['type']}:host={$this->config['host']};dbname={$this->config['database']};charset={$this->config['charset']};port={$this->config['port']}";
            $this->pdo = new \PDO($dsn, $this->config['username'], $this->config['password']);
            $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        } catch (\PDOException $e) {
            throw new \Exception("数据库连接失败: " . $e->getMessage());
        }
    }

    /**
     * 指定表名
     * @param string $table 表名
     * @return $this
     */
    public function table(string $table)
    {
        $this->table = $this->config['prefix'] . $table;
        return $this;
    }

    /**
     * 指定查询字段
     * @param string|array $field 字段列表
     * @return $this
     */
    public function field($field)
    {
        if (is_array($field)) {
            $this->field = implode(',', $field);
        } else {
            $this->field = $field;
        }
        return $this;
    }

    /**
     * 设置WHERE条件
     * @param string|array $condition 条件
     * @param mixed $value 值
     * @return $this
     */
    public function where($condition, $value = null)
    {
        if (is_array($condition)) {
            $where = [];
            foreach ($condition as $key => $val) {
                $param = ':where_' . md5($key . microtime());
                $where[] = "`{$key}` = {$param}";
                $this->bind[$param] = $val;
            }
            $this->where = 'WHERE ' . implode(' AND ', $where);
        } else {
            if ($value !== null) {
                $param = ':where_' . md5($condition . microtime());
                $this->where = "WHERE `{$condition}` = {$param}";
                $this->bind[$param] = $value;
            } else {
                $this->where = "WHERE {$condition}";
            }
        }
        return $this;
    }

    /**
     * 设置ORDER排序
     * @param string $order 排序条件
     * @return $this
     */
    public function order(string $order)
    {
        $this->order = "ORDER BY {$order}";
        return $this;
    }

    /**
     * 设置LIMIT限制
     * @param int $offset 偏移量
     * @param int $length 长度
     * @return $this
     */
    public function limit(int $offset, int $length = null)
    {
        if ($length !== null) {
            $this->limit = "LIMIT {$offset}, {$length}";
        } else {
            $this->limit = "LIMIT {$offset}";
        }
        return $this;
    }

    /**
     * 查询多条记录
     * @return array
     * @throws \Exception
     */
    public function select()
    {
        $this->buildSelectSql();
        
        try {
            $stmt = $this->pdo->prepare($this->sql);
            $stmt->execute($this->bind);
            $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            $this->reset();
            return $result;
        } catch (\PDOException $e) {
            throw new \Exception("查询失败: " . $e->getMessage() . " SQL: " . $this->sql);
        }
    }

    /**
     * 查询单条记录
     * @return array|null
     * @throws \Exception
     */
    public function find()
    {
        $this->limit(1);
        $this->buildSelectSql();
        
        try {
            $stmt = $this->pdo->prepare($this->sql);
            $stmt->execute($this->bind);
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
            $this->reset();
            return $result;
        } catch (\PDOException $e) {
            throw new \Exception("查询失败: " . $e->getMessage() . " SQL: " . $this->sql);
        }
    }

    /**
     * 插入数据
     * @param array $data 数据
     * @return int 插入ID
     * @throws \Exception
     */
    public function insert(array $data)
    {
        $fields = [];
        $placeholders = [];
        $this->bind = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "`{$key}`";
            $param = ':insert_' . md5($key . microtime());
            $placeholders[] = $param;
            $this->bind[$param] = $value;
        }
        
        $this->sql = "INSERT INTO {$this->table} (" . implode(',', $fields) . ") VALUES (" . implode(',', $placeholders) . ")";
        
        try {
            $stmt = $this->pdo->prepare($this->sql);
            $stmt->execute($this->bind);
            $insertId = $this->pdo->lastInsertId();
            $this->reset();
            return $insertId;
        } catch (\PDOException $e) {
            throw new \Exception("插入失败: " . $e->getMessage() . " SQL: " . $this->sql);
        }
    }

    /**
     * 更新数据
     * @param array $data 数据
     * @return int 受影响的行数
     * @throws \Exception
     */
    public function update(array $data)
    {
        $set = [];
        $this->bind = [];
        
        foreach ($data as $key => $value) {
            $param = ':update_' . md5($key . microtime());
            $set[] = "`{$key}` = {$param}";
            $this->bind[$param] = $value;
        }
        
        $this->sql = "UPDATE {$this->table} SET " . implode(',', $set) . " {$this->where}";
        
        try {
            $stmt = $this->pdo->prepare($this->sql);
            $stmt->execute($this->bind);
            $rowCount = $stmt->rowCount();
            $this->reset();
            return $rowCount;
        } catch (\PDOException $e) {
            throw new \Exception("更新失败: " . $e->getMessage() . " SQL: " . $this->sql);
        }
    }

    /**
     * 删除数据
     * @return int 受影响的行数
     * @throws \Exception
     */
    public function delete()
    {
        $this->sql = "DELETE FROM {$this->table} {$this->where}";
        
        try {
            $stmt = $this->pdo->prepare($this->sql);
            $stmt->execute($this->bind);
            $rowCount = $stmt->rowCount();
            $this->reset();
            return $rowCount;
        } catch (\PDOException $e) {
            throw new \Exception("删除失败: " . $e->getMessage() . " SQL: " . $this->sql);
        }
    }

    /**
     * 执行原生SQL查询
     * @param string $sql SQL语句
     * @param array $bind 参数绑定
     * @return array
     * @throws \Exception
     */
    public function query(string $sql, array $bind = [])
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($bind);
            return $stmt->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\PDOException $e) {
            throw new \Exception("查询失败: " . $e->getMessage() . " SQL: " . $sql);
        }
    }

    /**
     * 执行原生SQL命令
     * @param string $sql SQL语句
     * @param array $bind 参数绑定
     * @return int 受影响的行数
     * @throws \Exception
     */
    public function execute(string $sql, array $bind = [])
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($bind);
            return $stmt->rowCount();
        } catch (\PDOException $e) {
            throw new \Exception("执行失败: " . $e->getMessage() . " SQL: " . $sql);
        }
    }

    /**
     * 开始事务
     * @return bool
     */
    public function startTrans()
    {
        return $this->pdo->beginTransaction();
    }

    /**
     * 提交事务
     * @return bool
     */
    public function commit()
    {
        return $this->pdo->commit();
    }

    /**
     * 回滚事务
     * @return bool
     */
    public function rollback()
    {
        return $this->pdo->rollback();
    }

    /**
     * 构建查询SQL
     */
    protected function buildSelectSql()
    {
        $this->sql = "SELECT {$this->field} FROM {$this->table} {$this->where} {$this->order} {$this->limit}";
    }

    /**
     * 重置查询条件
     */
    protected function reset()
    {
        $this->sql = '';
        $this->bind = [];
        $this->where = '';
        $this->order = '';
        $this->limit = '';
        $this->field = '*';
    }

    /**
     * 获取最后执行的SQL
     * @return string
     */
    public function getLastSql()
    {
        return $this->sql;
    }

    /**
     * 静态创建实例
     * @param array $config 数据库配置
     * @return static
     * @throws \Exception
     */
    public static function instance(array $config = [])
    {
        return new static($config);
    }
}
?>
